package com.apexedu.eas.util;

import com.apexedu.framework.jdbc.DBConn;
import com.apexedu.framework.util.DBUtil;
import oracle.jdbc.OracleTypes;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import javax.servlet.jsp.jstl.sql.Result;
import javax.servlet.jsp.jstl.sql.ResultSupport;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by SwordRays on 2015-7-6.
 */
public class ProcUtil {
    private static final Log log = LogFactory.getLog(ProcUtil.class);

    /**
     * 存储过程(基础)(输入参数在前,输出参数在后)(游标返回为Result)
     *
     * @param conn
     * @param sql
     * @param inParamList
     * @param outParamList
     * @param isInFirst
     * @return
     */
    protected List<Object> executeProcedureInOutForResult(Connection conn, String sql, List<Object> inParamList, List<Integer> outParamList, Boolean isInFirst) {
        List<Object> resultList = null;
        CallableStatement cstmt = null;
        try {
            cstmt = conn.prepareCall(sql);
            /*
             * 设置输入输出参数
             */
            int index = 1;
            if (isInFirst) {
                for (int i = 0; null != inParamList && i < inParamList.size(); i++) {
                    cstmt.setObject(index++, inParamList.get(i));
                }
                for (int i = 0; null != outParamList && i < outParamList.size(); i++) {
                    cstmt.registerOutParameter(index++, outParamList.get(i));
                }
            } else {
                for (int i = 0; null != outParamList && i < outParamList.size(); i++) {
                    cstmt.registerOutParameter(index++, outParamList.get(i));
                }
                for (int i = 0; null != inParamList && i < inParamList.size(); i++) {
                    cstmt.setObject(index++, inParamList.get(i));
                }
            }

            /*
             * 执行
             */
            cstmt.executeQuery();

            /*
             * 封装结果为List
             */
            if (null != outParamList && outParamList.size() > 0) {
                // 输出参数集合，每一个对象代表一个输出参数（可以为字段，也可以为游标）
                resultList = new ArrayList<Object>();
                for (int i = 0; i < outParamList.size(); i++) {
                    // 取出一个输出参数
                    Object cursor = cstmt.getObject(i + 1);
                    // 如果是一个游标，则转换为Result对象
                    Object resultItem = procedureResultItem2ResultOrObject(cursor, outParamList.get(i));
                    // 将该参数放入结果list
                    resultList.add(resultItem);
                }
            }
        } catch (Exception e) {
            System.out.println("执行存储过程出错：" + e.getMessage());
            System.out.println("执行存储过程出错：" + sql);
            System.out.println("----------------------------------------------------------------");
            for (int i = 0; null != inParamList && i < inParamList.size(); i++) {
                System.out.println(i + " --- " + inParamList.get(i));
            }
            System.out.println("----------------------------------------------------------------");
        } finally {
            try {
                if (null != cstmt) {
                    cstmt.close();
                    cstmt = null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return resultList;
    }

    /**
     * 将存储过程结果(一个输出对象，可能是游标也可能是普通字段)转换为Result或Object
     *
     * @param resultItem
     * @param resultType
     * @return
     */
    private Object procedureResultItem2ResultOrObject(Object resultItem, Integer resultType) {
        Object newResultItem = null;
        if (OracleTypes.CURSOR == resultType) {
            /* 如果是游标，则每行中迭代每一列，放入一个对象，如此迭代每一行，放入一个对象集合。并将该对象集合作为输出参数 */
            ResultSet rs = (ResultSet) resultItem;
            Result result = ResultSupport.toResult(rs);
            // 关闭ResultSet资源
            try {
                rs.close();
                rs = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
            newResultItem = result;
        } else {
            /* 如果不是游标，则直接作为输出参数 */
            newResultItem = resultItem;
        }
        return newResultItem;
    }

    public static List<Object> callProcQueryIn(String proceduceName, Object... inParams) throws SQLException {
        Connection conn = null;
        String sql = "{call " + proceduceName + " }";
        List<Object> inParamList = null;
        List<Integer> outParamList = null;
        List<Object> resultList = null;
        try {
            conn = DBConn.getConnection();
            inParamList = new ArrayList<Object>();
            outParamList = new ArrayList<Integer>();

			/*转换输入参数为List*/
            if (null != inParams) {
                for (int i = 0; i < inParams.length; ++i) {
                    inParamList.add(inParams[i]);
                }
            }

			/*组装输出参数*/
            Integer inParamSize = 0;
            if (null != inParams) {
                inParamSize = inParams.length;
            }
            Integer leftIndex = proceduceName.indexOf("(");
            Integer rightIndex = proceduceName.indexOf(")");
            String tempStr = null;
            if (-1 != leftIndex && -1 != rightIndex) {
                tempStr = proceduceName.substring(leftIndex + 1, rightIndex);
            }
            Integer size = 0;
            if (null != tempStr && !"".equals(tempStr)) {
                size = tempStr.split(",").length;
            }
            Integer outParamSize = size - inParamSize;
            if (1 == outParamSize) {
                outParamList.add(OracleTypes.NUMBER);
            } else if (2 == outParamSize) {
                outParamList.add(OracleTypes.NUMBER);
                outParamList.add(OracleTypes.NVARCHAR);
            } else if (3 == outParamSize) {
                outParamList.add(OracleTypes.NUMBER);
                outParamList.add(OracleTypes.NVARCHAR);
                outParamList.add(OracleTypes.NVARCHAR);
            } else if (4 == outParamSize) {
                outParamList.add(OracleTypes.NUMBER);
                outParamList.add(OracleTypes.NVARCHAR);
                outParamList.add(OracleTypes.NVARCHAR);
                outParamList.add(OracleTypes.CURSOR);
            }

            resultList = new ProcUtil().executeProcedureInOutForResult(conn, sql, inParamList, outParamList, true);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(log, (ResultSet) null, null, conn);
        }
        return resultList;
    }

    public static List<Object> callProcQueryOut(String proceduceName, Object... inParams) throws SQLException {
        Connection conn = null;
        String sql = "{call " + proceduceName + " }";
        List<Object> inParamList = null;
        List<Integer> outParamList = null;
        List<Object> resultList = null;
        try {
            conn = DBConn.getConnection();
            inParamList = new ArrayList<Object>();
            outParamList = new ArrayList<Integer>();

			/*转换输入参数为List*/
            if (null != inParams) {
                for (int i = 0; i < inParams.length; ++i) {
                    inParamList.add(inParams[i]);
                }
            }

			/*组装输出参数*/
            Integer inParamSize = 0;
            if (null != inParams) {
                inParamSize = inParams.length;
            }
            Integer leftIndex = proceduceName.indexOf("(");
            Integer rightIndex = proceduceName.indexOf(")");
            String tempStr = null;
            if (-1 != leftIndex && -1 != rightIndex) {
                tempStr = proceduceName.substring(leftIndex + 1, rightIndex);
            }
            Integer size = 0;
            if (null != tempStr && !"".equals(tempStr)) {
                size = tempStr.split(",").length;
            }
            Integer outParamSize = size - inParamSize;
            if (1 == outParamSize) {
                outParamList.add(OracleTypes.NUMBER);
            } else if (2 == outParamSize) {
                outParamList.add(OracleTypes.NUMBER);
                outParamList.add(OracleTypes.NVARCHAR);
            } else if (3 == outParamSize) {
                outParamList.add(OracleTypes.NUMBER);
                outParamList.add(OracleTypes.NVARCHAR);
                outParamList.add(OracleTypes.NVARCHAR);
            } else if (4 == outParamSize) {
                outParamList.add(OracleTypes.NUMBER);
                outParamList.add(OracleTypes.NVARCHAR);
                outParamList.add(OracleTypes.NVARCHAR);
                outParamList.add(OracleTypes.CURSOR);
            }

            resultList = new ProcUtil().executeProcedureInOutForResult(conn, sql, inParamList, outParamList, false);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(log, (ResultSet) null, null, conn);
        }
        return resultList;
    }
    
}
